package com.daffodil.core.dao.helper;

import java.io.InputStream;
import java.io.Reader;
import java.lang.reflect.Field;
import java.sql.Blob;
import java.sql.Clob;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.persistence.Query;

import org.springframework.lang.Nullable;

import com.daffodil.core.entity.BaseEntity;
import com.daffodil.core.exception.BusinessException;
import com.daffodil.util.ReflectUtils;
import com.daffodil.util.StringUtils;

import lombok.extern.slf4j.Slf4j;

/**
 * Dao帮助类
 * 
 * @author yweijian
 * @date 2022年3月3日
 * @version 1.0
 * @description
 */
@Slf4j
public class DaoHelper {

    /** 空格 */
    private static final String SPACES = " ";
    
    /** 匹配查询主体 */
    public static final Pattern SUBJECT_MATCH = Pattern.compile("select\\s+.*?\\s+from", Pattern.CASE_INSENSITIVE);
    
    /** 匹配distinct的去重查询主体 */
    public static final Pattern DISTINCT_MATCH = Pattern.compile("select\\s+(distinct\\s+.*?)\\s+from", Pattern.CASE_INSENSITIVE);
    
    /** 匹配distinct的去重查询构造主体 */
    public static final Pattern CONSTRUCT_MATCH = Pattern.compile("select\\s+distinct\\s+new\\s+.*?\\((.*?)\\)\\s+from", Pattern.CASE_INSENSITIVE);
    
    /** 匹配group by 忽略“(”括号内“)”的匹配 */
    public static final Pattern GROUP_MATCH = Pattern.compile("from\\s+(.*?(?![^\\(]*\\)))\\s+(group\\s+by)", Pattern.CASE_INSENSITIVE);
    
    /** 匹配order by 忽略“(”括号内“)”的匹配 */
    public static final Pattern ORDER_MATCH = Pattern.compile("from\\s+(.*?(?![^\\(]*\\)))\\s+(order\\s+by)", Pattern.CASE_INSENSITIVE);
    
    /** 匹配统计查询主体 */
    public static final Pattern COUNT_MATCH = Pattern.compile("select\\s+count\\(.*?\\)\\s+.*?from", Pattern.CASE_INSENSITIVE);

    /**
     * -将查询语句中的占位符[?]替换成[?i]<br>
     * -如：from table where id = ? and name = ?<br>
     * -> from table where id = ?0 and name = ?1
     * 
     * @param hql
     * @return
     */
    public static String getQueryHql(String hql) {
        return getQuerySql(hql);
    }

    /**
     * -将查询语句中的占位符[?]替换成[?i]<br>
     * -如：from table where id = ? and name = ?<br>
     * -> from table where id = ?0 and name = ?1
     * 
     * @param sql
     * @return
     */
    public static String getQuerySql(String sql) {
        StringBuffer sb = new StringBuffer();
        Pattern pattern = Pattern.compile("[?]");
        Matcher matcher = pattern.matcher(sql);
        boolean result = matcher.find();
        int count = 0;
        while (result) {
            matcher.appendReplacement(sb, "?" + count);
            count++;
            result = matcher.find();
        }
        matcher.appendTail(sb);
        return sb.toString();
    }

    /**
     * -设置query参数
     * 
     * @param query
     * @param paras
     * @throws BusinessException
     */
    public static void setQueryParas(Query query, List<Object> paras) throws BusinessException {
        if ((paras != null) && (paras.size() > 0)) {
            for (int i = 0; i < paras.size(); i++) {
                query.setParameter(i, paras.get(i));
            }
        }
    }

    /**
     * -获取count的hql
     * 
     * @param hql
     * @return
     */
    public static String getQueryCountHql(String hql) {
        return getQueryCountHql(hql, null);
    }
    
    /**
     * -获取count的hql
     * @param hql
     * @param distinctField 去重属性字段
     * @return
     */
    public static String getQueryCountHql(String hql, @Nullable String distinctField) {
        StringBuilder builder = new StringBuilder();
        hql = StringUtils.isNotEmpty(hql) ? hql.trim() : "";
        Matcher countMatcher = DaoHelper.COUNT_MATCH.matcher(hql);
        if(countMatcher.find()) {
            return hql;
        }
        Matcher subjectMatcher = DaoHelper.SUBJECT_MATCH.matcher(hql);
        if(subjectMatcher.find()) {
            String subject = subjectMatcher.group();
            String secondary = hql.replace(subject, "");
            Matcher distinctMatcher = DaoHelper.DISTINCT_MATCH.matcher(subject);
            if(distinctMatcher.find()) {
                if(StringUtils.isNotEmpty(distinctField)) {
                    builder.append("select count(distinct ");
                    builder.append(distinctField.trim());
                    builder.append(") ");
                }else {
                    Matcher constructMatcher = DaoHelper.CONSTRUCT_MATCH.matcher(subject);
                    if(constructMatcher.find()) {
                        String[] fields = constructMatcher.group(1).split(",");
                        builder.append("select count(distinct ");
                        builder.append(fields[0].trim());
                        builder.append(") ");
                    }else {
                        builder.append("select count(");
                        builder.append(distinctMatcher.group(1).trim());
                        builder.append(") ");
                    }
                }
            }else {
                builder.append("select count(*) ");
            }
            
            Matcher groupMatcher = DaoHelper.GROUP_MATCH.matcher(hql);
            if(groupMatcher.find()) {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(groupMatcher.group());
                builder.append("from ");
                builder.append(orderMatcher.find() ? orderMatcher.group(1).trim() : groupMatcher.group(1).trim());
            }else {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(hql);
                builder.append("from ");
                builder.append(orderMatcher.find() ? orderMatcher.group(1).trim() : secondary.trim());
            }
        }else {
            builder.append("select count(*) ");
            Matcher groupMatcher = DaoHelper.GROUP_MATCH.matcher(hql);
            if(groupMatcher.find()) {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(groupMatcher.group());
                builder.append("from ");
                builder.append(orderMatcher.find() ? orderMatcher.group(1).trim() : groupMatcher.group(1).trim());
            }else {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(hql);
                if(orderMatcher.find()) {
                    builder.append("from ");
                    builder.append(orderMatcher.group(1).trim());
                }else {
                    builder.append(hql.trim());
                }
            }
        }
        return builder.toString();
    }

    /**
     * -获取count的sql
     * 
     * @param hql
     * @return
     */
    public static String getQueryCountSql(String sql) {
        StringBuilder builder = new StringBuilder();
        sql = StringUtils.isNotEmpty(sql) ? sql.trim() : "";
        Matcher countMatcher = DaoHelper.COUNT_MATCH.matcher(sql);
        if(countMatcher.find()) {
            return sql;
        }
        Matcher subjectMatcher = DaoHelper.SUBJECT_MATCH.matcher(sql);
        if(subjectMatcher.find()) {
            String subject = subjectMatcher.group();
            builder.append("select count(*) as count from (");
            Matcher groupMatcher = DaoHelper.GROUP_MATCH.matcher(sql);
            if(groupMatcher.find()) {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(groupMatcher.group());
                builder.append(subject.trim() + SPACES);
                builder.append(orderMatcher.find() ? orderMatcher.group(1).trim() : groupMatcher.group(1).trim());
            }else {
                Matcher orderMatcher = DaoHelper.ORDER_MATCH.matcher(sql);
                if(orderMatcher.find()) {
                    builder.append(subject.trim() + SPACES);
                    builder.append(orderMatcher.group(1).trim());
                }else {
                    builder.append(sql.trim());
                }
            }
            builder.append(") x");
        }
        return builder.toString();
    }

    /**
     * -类对象合并
     * 
     * @param sourceBean
     * @param targetBean
     * @return
     */
    public static BaseEntity<?> combineBean(BaseEntity<?> sourceBean, BaseEntity<?> targetBean) {
        Field[] sourceFields = sourceBean.getClass().getDeclaredFields();
        for (int i = 0; i < sourceFields.length; i++) {
            Field sourceField = sourceFields[i];
            if (!"serialVersionUID".equals(sourceField.getName())) {
                Object value = ReflectUtils.invokeGetter(sourceBean, sourceField.getName());
                if(null != value) {
                    ReflectUtils.invokeSetter(targetBean, sourceField.getName(), value);
                }
            }
        }
        return targetBean;
    }

    /**
     * 
     * @param o
     * @return
     * @throws BusinessException
     */
    public static String objectDataToString(Object o) throws BusinessException {
        if (o == null) {
            return "";
        }
        if (o instanceof Clob) {
            return clobToString((Clob) o);
        }
        if (o instanceof java.sql.Blob) {
            return blobToString((Blob) o);
        }
        return o.toString();
    }

    /**
     * -把大对象CLOB转换成String
     * 
     * @param clob
     * @return
     */
    public static String clobToString(Clob clob) {
        if (clob == null) {
            return "";
        }
        StringBuffer buffer = new StringBuffer();
        Reader clobStream = null;
        try {
            clobStream = clob.getCharacterStream();
            char[] c = new char[8192];
            int i = 0;
            while ((i = clobStream.read(c)) != -1) {
                buffer.append(c, 0, i);
            }
            return buffer.toString();
        } catch (Exception e) {
            throw new BusinessException("[JDBC] Clob数据转换错误 ...", e);
        } finally {
            try {
                if (clobStream != null) {
                    clobStream.close();
                }
            } catch (Exception e) {
                log.warn(e.getMessage(), e);
            }
        }
    }

    /**
     * -把大对象BLOB转换成String
     * 
     * @param blob
     * @return
     */
    public static String blobToString(Blob blob) {
        StringBuffer buffer = new StringBuffer();
        InputStream in = null;
        try {
            in = blob.getBinaryStream();
            byte[] b = new byte[(int) blob.length()];
            for (; (in.read(b)) > 0;) {
                buffer = buffer.append(new String(b));
            }
            return buffer.toString();
        } catch (Exception e) {
            throw new BusinessException("[JDBC] Blob数据转换错误 ...", e);
        } finally {
            try {
                if(in != null) {
                    in.close();
                }
            } catch (Exception e) {
                log.warn(e.getMessage(), e);
            }
        }
    }

}
